using System;
using System.Collections.Generic;
using System.Text;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;

namespace CodeGenerator.Classes
{
    public class GeneratorClassDua
    {
        private string mTableName = "";
        private string connStr = "";
        private string mKolomName = "";

        public static string name;
        public static string nameprojek;

        bool MaxID = false;
        //public string Namespace
        //{
            //get { return name;}
            //set { name=value;}
        //}

        public GeneratorClassDua(string connectionString, string TableName)
        {
            mTableName = TableName;
            connStr = connectionString;
        }
        private string CreateProperty(string type)
        {
            string ret = "";
            string dataType = string.Empty;

            SqlConnection conn = new SqlConnection(connStr);
            SqlCommand cmd = new SqlCommand("SELECT Column_Name,Data_Type From information_Schema.columns Where Table_Name='" + mTableName + "'", conn);
            SqlDataReader rdr;

            try
            {
                conn.Open();
                rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    if (type == "C#")
                    {
                        dataType = rdr[1].ToString();
                        switch(dataType.Trim().ToLower())
                        {
                            case "varchar":
                                dataType = "string";
                                break;

                            case "decimal":
                                dataType = "decimal";
                                break;


                            case "datetime":
                                dataType = "DateTime?";
                                break;

                            case "char":
                                dataType = "string";
                                break;

                            case "text":
                                dataType = "string";
                                break;

                            case "numeric":
                                dataType = "int";
                                break;

                            case "int":
                                dataType = "int";
                                break;


                            default:
                                dataType = "string";
                                break;
                        }
                        ret += "\t\tprivate "+ dataType +" _" + rdr[0].ToString().ToLower() + ";" +
                                "\n" +
                                "\t\tpublic "+ dataType +" "+ rdr[0].ToString() + "\n" +
                                "\t\t{\n" +
                                "\t\t\tget { return _" + rdr[0].ToString().ToLower() + "; }\n" +
                                "\t\t\tset { _" + rdr[0].ToString().ToLower() + " = value; }\n" +
                                "\t\t}\n";
                    }

                    else if (type == "VB")
                    {
                        ret += "\t\tPrivate m_" + rdr[0].ToString() + " As String" +
                                "\n" +
                                "\t\tProperty " + rdr[0].ToString() + " As String\n" +
                                "\t\t\tGet\n" +
                                "\t\t\t\tReturn m_" + rdr[0].ToString() + "\n" +
                                "\t\t\tEnd Get\n" +
                                "\t\t\tSet(ByVal value As String)\n" +
                                "\t\t\t\tm_" + rdr[0].ToString() + " = value\n" +
                                "\t\t\tEnd Set\n" +
                                "\t\tEnd Property\n\n";
                    }
                }
            }
            catch
            { }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
                conn.Dispose();
                cmd.Dispose();
            }


            return ret;
        }


        private string CreateInsertAjaxHandler()
        {
            StringBuilder sql = new StringBuilder();
            sql.AppendLine("private string Insert(HttpContext context)");
            sql.AppendLine("\t{");
            sql.AppendLine("\t\tJSONHelper helper = new JSONHelper();");
            sql.AppendLine("\t\t"+PropCaseStartLower(mTableName).Replace("_", "") + " obj = new " + PropCaseStartLower(mTableName).Replace("_", "") + "();");
            
            string ret = "";
            string dataType = string.Empty;

            SqlConnection conn = new SqlConnection(connStr);
            SqlCommand cmd = new SqlCommand("SELECT Column_Name,Data_Type From information_Schema.columns Where Table_Name='" + mTableName + "'", conn);
            SqlDataReader rdr;

            try
            {
                conn.Open();
                rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                        dataType = rdr[1].ToString();
                        switch (dataType.Trim().ToLower())
                        {
                            case "varchar":
                                //dataType = "string";
                                dataType = "Convert.ToString(";
                                break;

                            case "decimal":
                                dataType = "Convert.ToDecimal(";
                                break;
                                

                            case "datetime":
                                dataType = "Convert.ToDateTime(";
                                break;
                                
                            case "char":
                                dataType = "Convert.ToString(";
                                break;

                            case "text":
                                dataType = "Convert.ToString(";
                                break;

                            case "numeric":
                                dataType = "Convert.ToInt32(";
                                break;
                               
                            case "int":
                                dataType = "Convert.ToInt32(";
                                break;

                            default:
                                dataType = "Convert.ToString(";
                                break;
                        }

                        sql.AppendLine("\t\tobj." + PropCaseStartLower(rdr[0].ToString()).Replace("_", "") + " = " + dataType + "context.Request.Params[" + "\"" + PropCaseStartLower(rdr[0].ToString()).Replace("_", "") + "\"" + "]);");
                   
                }

                sql.AppendLine("\n");
                sql.AppendLine("\t\tif (" + PropCaseStartLower(mTableName.Replace("_", "")) + "Dal.Save(obj))");
                sql.AppendLine("\t\t\t{");
                sql.AppendLine("\t\t\t\thelper.Data = obj;");
                sql.AppendLine("\t\t\t\thelper.Message = " + "\"" + "Data has been saved!" + "\"" + ";");
                sql.AppendLine("\t\t\t\thelper.IsSuccess = true;");
                sql.AppendLine("\t\t\t\tlog = new sysTLog();");
                sql.AppendLine("\t\t\t\tlog.logEvent = " + "\"" + "Insert " + PropCaseStartLower(mTableName).Replace("_", "") + "Success" + "\"" + ";");
                sql.AppendLine("\t\t\t\tlog.logMessage = roleDal.GetErrorMessage();");
                sql.AppendLine("\t\t\t}");
                sql.AppendLine("\t\telse");
                sql.AppendLine("\t\t\t{");
                sql.AppendLine("\t\t\t\thelper.IsSuccess = false;");
                sql.AppendLine("\t\t\t\thelper.Message = roleDal.GetErrorMessage();");
                sql.AppendLine("\t\t\t\tlog = new sysTLog();");
                sql.AppendLine("\t\t\t\tlog.logEvent = " + "\"" + "Insert " + PropCaseStartLower(mTableName).Replace("_", "") + "Error" + "\"" + ";");
                sql.AppendLine("\t\t\t\tlog.logMessage = roleDal.GetErrorMessage();");
                sql.AppendLine("\t\t\t}");
                sql.AppendLine("\n");
                sql.AppendLine("\t\t\t\tlog.logSource = " + "\"" + PropCaseStartLower(mTableName) + ".ashx" + "\"" + ";");
                sql.AppendLine("\t\t\t\tlog.createdBy = base.UserLoggedIn;");
                sql.AppendLine("\t\t\t\tlog.createdDate = DateTime.Now;");
                sql.AppendLine("\t\t\t\tlogDal.Save(log);");
                sql.AppendLine("\t\t\t\treturn Newtonsoft.Json.JsonConvert.SerializeObject(helper);");
                sql.AppendLine("\t}");

}

            catch
            { }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
                conn.Dispose();
                cmd.Dispose();
            }


            return sql.ToString();
        }

        private string CreateUpdateAjaxHandler()
        {
            StringBuilder sql = new StringBuilder();
            sql.AppendLine("private string Update(HttpContext context)");
            sql.AppendLine("\t{");
            sql.AppendLine("\t\tJSONHelper helper = new JSONHelper();");
            sql.AppendLine("\t\t" + PropCaseStartLower(mTableName).Replace("_", "") + " obj = new " + PropCaseStartLower(mTableName).Replace("_", "") + "();");

            string ret = "";
            string dataType = string.Empty;

            SqlConnection conn = new SqlConnection(connStr);
            SqlCommand cmd = new SqlCommand("SELECT Column_Name,Data_Type From information_Schema.columns Where Table_Name='" + mTableName + "'", conn);
            SqlDataReader rdr;

            try
            {
                conn.Open();
                rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    dataType = rdr[1].ToString();
                    switch (dataType.Trim().ToLower())
                    {
                        case "varchar":
                            dataType = "Convert.ToString(";
                            break;

                        case "decimal":
                            dataType = "Convert.ToDecimal(";
                            break;


                        case "datetime":
                            dataType = "Convert.ToDateTime(";
                            break;

                        case "char":
                            dataType = "Convert.ToString(";
                            break;

                        case "text":
                            dataType = "Convert.ToString(";
                            break;

                        case "numeric":
                            dataType = "Convert.ToInt32(";
                            break;

                        case "int":
                            dataType = "Convert.ToInt32(";
                            break;

                        default:
                            dataType = "Convert.ToString(";
                            break;
                    }

                    sql.AppendLine("\t\tobj." + PropCaseStartLower(rdr[0].ToString()).Replace("_", "") + " = " + dataType + "context.Request.Params[" + "\"" + PropCaseStartLower(rdr[0].ToString()).Replace("_", "") + "\"" + "]);");

                }

                sql.AppendLine("\n");
                sql.AppendLine("\t\tif (" + PropCaseStartLower(mTableName.Replace("_", "")) + "Dal.Update(obj))");
                sql.AppendLine("\t\t\t{");
                sql.AppendLine("\t\t\t\thelper.Data = obj;");
                sql.AppendLine("\t\t\t\thelper.Message = " + "\"" + "Data has been saved!" + "\"" + ";");
                sql.AppendLine("\t\t\t\thelper.IsSuccess = true;");
                sql.AppendLine("\t\t\t\tlog = new sysTLog();");
                sql.AppendLine("\t\t\t\tlog.logEvent = " + "\"" + "Update " + PropCaseStartLower(mTableName).Replace("_", "") + "Success" + "\"" + ";");
                sql.AppendLine("\t\t\t\tlog.logMessage = roleDal.GetErrorMessage();");
                sql.AppendLine("\t\t\t}");
                sql.AppendLine("\t\telse");
                sql.AppendLine("\t\t\t{");
                sql.AppendLine("\t\t\t\thelper.IsSuccess = false;");
                sql.AppendLine("\t\t\t\thelper.Message = roleDal.GetErrorMessage();");
                sql.AppendLine("\t\t\t\tlog = new sysTLog();");
                sql.AppendLine("\t\t\t\tlog.logEvent = " + "\"" + "Update " + PropCaseStartLower(mTableName).Replace("_", "") + "Error" + "\"" + ";");
                sql.AppendLine("\t\t\t\tlog.logMessage = roleDal.GetErrorMessage();");
                sql.AppendLine("\t\t\t}");
                sql.AppendLine("\n");
                sql.AppendLine("\t\t\t\tlog.logSource = " + "\"" + PropCaseStartLower(mTableName) + ".ashx" + "\"" + ";");
                sql.AppendLine("\t\t\t\tlog.createdBy = base.UserLoggedIn;");
                sql.AppendLine("\t\t\t\tlog.createdDate = DateTime.Now;");
                sql.AppendLine("\t\t\t\tlogDal.Save(log);");
                sql.AppendLine("\t\t\t\treturn Newtonsoft.Json.JsonConvert.SerializeObject(helper);");
                sql.AppendLine("\t}");

            }

            catch
            { }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
                conn.Dispose();
                cmd.Dispose();
            }


            return sql.ToString();
        }

        private string CreateDeleteAjaxHandler()
        {
            StringBuilder sql = new StringBuilder();
            try
            {
                sql.AppendLine("private string Delete(HttpContext context)");
                sql.AppendLine("\t{");
                sql.AppendLine("\t\tJSONHelper helper = new JSONHelper();");
                sql.AppendLine("\t\tbool isSuccess = true;");
                sql.AppendLine("\t\tstring[] ids = context.Request.Params["+"\""+"id"+"\""+"].Split(',');");
                sql.AppendLine("\t\tforeach (string id in ids)");
                sql.AppendLine("\t\t{");
                sql.AppendLine("\t\tint intID = Convert.ToInt32(id);");
                sql.AppendLine("\t\tisSuccess = "+  PropCaseStartLower(mTableName.Replace("_", "")) + "Dal.Delete(intID);");
                sql.AppendLine("\t\tif (!isSuccess) break;");
                sql.AppendLine("\t\t}");
                sql.AppendLine("\n");
                sql.AppendLine("\t\tif (isSuccess)");
                sql.AppendLine("\t\t\t{");
                sql.AppendLine("\t\t\t\thelper.Data = obj;");
                sql.AppendLine("\t\t\t\thelper.Message = " + "\"" + "Data has been saved!" + "\"" + ";");
                sql.AppendLine("\t\t\t\thelper.IsSuccess = true;");
                sql.AppendLine("\t\t\t\tlog = new sysTLog();");
                sql.AppendLine("\t\t\t\tlog.logEvent = " + "\"" + "Delete " + PropCaseStartLower(mTableName).Replace("_", "") + "Success" + "\"" + ";");
                sql.AppendLine("\t\t\t\tlog.logMessage = roleDal.GetErrorMessage();");
                sql.AppendLine("\t\t\t}");
                sql.AppendLine("\t\telse");
                sql.AppendLine("\t\t\t{");
                sql.AppendLine("\t\t\t\thelper.IsSuccess = false;");
                sql.AppendLine("\t\t\t\thelper.Message = roleDal.GetErrorMessage();");
                sql.AppendLine("\t\t\t\tlog = new sysTLog();");
                sql.AppendLine("\t\t\t\tlog.logEvent = " + "\"" + "Delete " + PropCaseStartLower(mTableName).Replace("_", "") + "Error" + "\"" + ";");
                sql.AppendLine("\t\t\t\tlog.logMessage = roleDal.GetErrorMessage();");
                sql.AppendLine("\t\t\t}");
                sql.AppendLine("\n");
                sql.AppendLine("\t\t\t\tlog.logSource = " + "\"" + PropCaseStartLower(mTableName) + ".ashx" + "\"" + ";");
                sql.AppendLine("\t\t\t\tlog.createdBy = base.UserLoggedIn;");
                sql.AppendLine("\t\t\t\tlog.createdDate = DateTime.Now;");
                sql.AppendLine("\t\t\t\tlogDal.Save(log);");
                sql.AppendLine("\t\t\t\treturn Newtonsoft.Json.JsonConvert.SerializeObject(helper);");
                sql.AppendLine("\t}");
            }

            catch
            { }
           


            return sql.ToString();
        }

        private string CreateInsert(string LanguageType)
        {
            string ret = "";
            if (LanguageType == "C#")
            {
                string tValue = "";
                string m_value = "string ";

                SqlConnection conn = new SqlConnection(connStr);
                SqlCommand cmd = new SqlCommand("SELECT Column_Name From information_Schema.columns Where Table_Name='" + mTableName + "'", conn);
                SqlDataReader rdr;
                try
                {
                    conn.Open();
                    rdr = cmd.ExecuteReader();
                    while (rdr.Read())
                    {
                        m_value += rdr[0].ToString() + " , string ";
                        tValue += "\t\t\tcmd.Parameters.AddWithValue(\"@" + rdr[0].ToString() + "\" ," + rdr[0].ToString() + " );\n";
                    }
                    m_value = m_value.Substring(0, m_value.Length - 10);
                }
                catch
                { }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                    }
                    conn.Dispose();
                    cmd.Dispose();
                }


                ret += "\t\tpublic void Insert(" + m_value + ")\n" +
                                "\t\t{\n" +
                                "\t\t\tSqlConnection conn = new SqlConnection(ConnectionString)\n" +
                                "\t\t\tSqlCommand cmd = new SqlCommand(\"Insert_" + mTableName + "\", conn);\n" +
                                "\t\t\tcmd.CommandType = CommandType.StoredProcedure;\n" +
                                tValue +
                                "\n" +
                                "\t\t\ttry\n" +
                                "\t\t\t{\n" +
                                "\t\t\t\tconn.Open();\n" +
                                "\t\t\t\tcmd.ExecuteNonQuery();\n" +
                                "\t\t\t}\n" +
                                "\t\t\tcatch\n" +
                                "\t\t\t{}\n" +
                                "\t\t\tfinally\n" +
                                "\t\t\t{\n" +
                                "\t\t\t\tif (conn.State == ConnectionState.Open) conn.Close();\n" +
                                "\t\t\t\tconn.Dispose();\n" +
                                "\t\t\t\tcmd.Dispose();\n" +
                                "\t\t\t}\n" +
                                "\t\t}\n";
            }
            else if (LanguageType == "VB")
            {
                string tValue = "";
                string m_value = "ByVal ";

                SqlConnection conn = new SqlConnection(connStr);
                SqlCommand cmd = new SqlCommand("SELECT Column_Name From information_Schema.columns Where Table_Name='" + mTableName + "'", conn);
                SqlDataReader rdr;
                try
                {
                    conn.Open();
                    rdr = cmd.ExecuteReader();
                    while (rdr.Read())
                    {
                        m_value += rdr[0].ToString() + " As String , ByVal ";
                        tValue += "\t\t\tcmd.Parameters.AddWithValue(\"@" + rdr[0].ToString() + "\" ," + rdr[0].ToString() + " )\n";
                    }
                    m_value = m_value.Substring(0, m_value.Length - 9);
                }
                catch
                { }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                    }
                    conn.Dispose();
                    cmd.Dispose();
                }


                ret += "\t\tPublic Sub Insert(" + m_value + ")\n" +
                        "\t\t\tDim conn As New SqlConnection(ConnectionString)\n" +
                        "\t\t\tDim cmd As New SqlCommand(\"Insert_" + mTableName + "\", conn)\n" +
                        "\t\t\tcmd.CommandType = CommandType.StoredProcedure\n" +
                        tValue +
                        "\n" +
                        "\t\t\tTry\n" +
                        "\t\t\t\tconn.Open()\n" +
                        "\t\t\t\tcmd.ExecuteNonQuery()\n" +
                        "\t\t\tCatch\n" +
                        "\t\t\tFinally\n" +
                        "\t\t\t\tIf conn.State = ConnectionState.Open Then conn.Close()\n" +
                        "\t\t\t\tconn.Dispose()\n" +
                        "\t\t\t\tcmd.Dispose()\n" +
                        "\t\t\tEnd Try\n" +
                        "\t\tEnd Sub\n";

            }

            return ret;
        }

        private string CreateInsertEntity()
        {
            string ret = "";
            
                string tValue = "";
                string m_value = "string ";
                string dataType = string.Empty;
                string primary;


                string insertMaxID = "";


                SqlConnection conn = new SqlConnection(connStr);
                SqlCommand cmd = new SqlCommand("SELECT Column_Name,Data_Type From information_Schema.columns Where Table_Name='" + mTableName + "'", conn);
                SqlDataReader rdr;
                try
                {
                    conn.Open();
                    rdr = cmd.ExecuteReader();
                    primary = GetPrimaryKeyTable(mTableName);
                    while (rdr.Read())
                    {

                        dataType = rdr[1].ToString();
                        switch (dataType.Trim().ToLower())
                        {
                            case "varchar":
                                tValue += "\t\t\t s.AppendLine(" +"\"'\""+ "+ " + mTableName.ToLower().Trim() + "." + rdr[0].ToString() + " +" + "\"',\""+ ");\n";
                                break;

                            case "decimal":
                                tValue += "\t\t\t s.AppendLine(" + mTableName.ToLower().Trim() + "." + rdr[0].ToString() + " +\",\");\n";

                                break;


                            case "datetime":
                                tValue += "\t\t\t s.AppendLine(" + "\"'\"" + "+ " + mTableName.ToLower().Trim() + "." + rdr[0].ToString() + " +" + "\"',\"" + ");\n";
                                break;

                            case "char":
                                tValue += "\t\t\t s.AppendLine(" + "\"'\"" + "+ " + mTableName.ToLower().Trim() + "." + rdr[0].ToString() + " +" + "\"',\"" + ");\n";
                                break;

                            case "text":
                                tValue += "\t\t\t s.AppendLine(" + "\"'\"" + "+ " + mTableName.ToLower().Trim() + "." + rdr[0].ToString() + " +" + "\"',\"" + ");\n";
                                break;

                            case "numeric":
                                tValue += "\t\t\t s.AppendLine(" + mTableName.ToLower().Trim() + "." + rdr[0].ToString() + " +\",\");\n";

                                break;

                            case "int":
                                if (primary.Trim() == rdr[0].ToString())
                                {
                                    MaxID = true;
                                    mKolomName = rdr[0].ToString().Trim();

                                    tValue += "\t\t\t s.AppendLine(" + "GetDataMaxID()" + " +\",\");\n";

                                    //insertMaxID = CreateGetMaxID(rdr[0].ToString().Trim(), mTableName.Trim());
                                }
                                else
                                {
                                    tValue += "\t\t\t s.AppendLine(" + mTableName.ToLower().Trim() + "." + rdr[0].ToString() + " +\",\");\n";
                                }

                                break;


                        }

                        }

                    tValue = tValue.Substring(0, tValue.Length - 5)+ "\"" +");" ;
                }
                catch
                { }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                    }
                    conn.Dispose();
                    cmd.Dispose();
                }


                ret += "\t\tpublic int Insert(" + mTableName +" "+mTableName.ToLower() + ")\n" +
                       "\t\t{\n" +
                       "\t\t\t StringBuilder s = new StringBuilder();\n" +
                       ReturnSQLInsert()+
                       tValue +
                        "\n\t\t\t s.AppendLine(\"" + ")" + "\"" + ");\n"+
                       "\n\t\t\t int result = -1;\n"+
                       "\t\t\t accessor = new Accessor(true, connString);\n"+
                       "\t\t\t accessor.CommandText = s.ToString();\n"+//\""+mTableName.Trim()+"_Insert\""+";\n"+
                       "\t\t\t accessor.CommandType = CommandType.Text;\n"+
                       "\n" +
                       "\t\t\t result = Excutor.ExecuteNonQuery(accessor);\n"+
                       "\t\t\t if (result != -1)\n"+
                       "\t\t\t\t accessor.CommitTransaction();\n"+
                       "\t\t\t else\n"+
                       "\t\t\t\t accessor.RollBackTransaction();\n"+
                       "\t\t\t return result;\n"+
                       "\t\t}\n";
         
            return ret;
        }

        private string CreateGetMaxID(string kolom,string tabel)
        {
            string query ="";
            query = " \t\tpublic int GetDataMaxID() \n" +
                      "\t\t { \n" +
                      "\t\t\t  StringBuilder s = new StringBuilder();\n " +
                      "\t\t\t  s.AppendLine(\"" + " SELECT CASE WHEN MAX(" + kolom + ") IS NULL THEN 0 ELSE MAX(" + kolom + ") END AS " + kolom + " \"" + ");\n" +
                      "\t\t\t  s.AppendLine(\"" + " FROM " + tabel + " \"" + ");\n" +
                       "\n" +
                      "\t\t\t  int result = 0;\n" +
                      "\t\t\t  accessor = new Accessor(true, connString);\n" +
                      "\t\t\t  accessor.CommandText = s.ToString();\n" +
                      "\t\t\t  accessor.CommandType = CommandType.Text;\n" +
                      "\n" +
                     "\t\t\t  object rdr = Excutor.ExecuteScalar(accessor);\n" +
                     "\t\t\t  if (rdr != null) \n" +
                     "\t\t\t\t {\n " +
                     "\t\t\t\t\t   result= Convert.ToInt32(Convert.ToInt32(rdr) + 1);\n" +
                     "\t\t\t\t } \n" +
                     "\n" +
                     "\t\t return result;\n" +
                     "\n" +
                     "\t\t } ";

            return query;
        
        }

        private string CreateUpdate(string LanguageType)
        {
            string ret = "";
            if (LanguageType == "C#")
            {


                string tValue = "";
                string m_value = "string ";

                SqlConnection conn = new SqlConnection(connStr);
                SqlCommand cmd = new SqlCommand("SELECT Column_Name From information_Schema.columns Where Table_Name='" + mTableName + "'", conn);
                SqlDataReader rdr;
                try
                {
                    conn.Open();
                    rdr = cmd.ExecuteReader();
                    while (rdr.Read())
                    {
                        m_value += rdr[0].ToString() + " , string ";
                        tValue += "\t\t\tcmd.Parameters.AddWithValue(@\"" + rdr[0].ToString() + "\" ," + rdr[0].ToString() + " );\n";
                    }
                    m_value = m_value.Substring(0, m_value.Length - 10);
                }
                catch
                { }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                    }
                    conn.Dispose();
                    cmd.Dispose();
                }


                ret += "\t\tpublic void Update(" + m_value + ")\n" +
                                "\t\t{\n" +
                                "\t\t\tSqlConnection conn = new SqlConnection(ConnectionString)\n" +
                                "\t\t\tSqlCommand cmd = new SqlCommand(\"Update_" + mTableName + "\", conn);\n" +
                                "\t\t\tcmd.CommandType = CommandType.StoredProcedure;\n" +
                                tValue +
                                "\n" +
                                "\t\t\ttry\n" +
                                "\t\t\t{\n" +
                                "\t\t\t\tconn.Open();\n" +
                                "\t\t\t\tcmd.ExecuteNonQuery();\n" +
                                "\t\t\t}\n" +
                                "\t\t\tcatch\n" +
                                "\t\t\t{}\n" +
                                "\t\t\tfinally\n" +
                                "\t\t\t{\n" +
                                "\t\t\t\tif (conn.State == ConnectionState.Open) conn.Close();\n" +
                                "\t\t\t\tconn.Dispose();\n" +
                                "\t\t\t\tcmd.Dispose();\n" +
                                "\t\t\t}\n" +
                                "\t\t}\n";
            }
            else if (LanguageType == "VB")
            {
                string tValue = "";
                string m_value = "ByVal ";

                SqlConnection conn = new SqlConnection(connStr);
                SqlCommand cmd = new SqlCommand("SELECT Column_Name From information_Schema.columns Where Table_Name='" + mTableName + "'", conn);
                SqlDataReader rdr;
                try
                {
                    conn.Open();
                    rdr = cmd.ExecuteReader();
                    while (rdr.Read())
                    {
                        m_value += rdr[0].ToString() + " As String , ByVal ";
                        tValue += "\t\t\tcmd.Parameters.AddWithValue(\"@" + rdr[0].ToString() + "\" ," + rdr[0].ToString() + " )\n";
                    }
                    m_value = m_value.Substring(0, m_value.Length - 9);
                }
                catch
                { }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                    }
                    conn.Dispose();
                    cmd.Dispose();
                }


                ret += "\t\tPublic Sub Update(" + m_value + ")\n" +
                        "\t\t\tDim conn As New SqlConnection(ConnectionString)\n" +
                        "\t\t\tDim cmd As New SqlCommand(\"Update_" + mTableName + "\", conn)\n" +
                        "\t\t\tcmd.CommandType = CommandType.StoredProcedure\n" +
                        tValue +
                        "\n" +
                        "\t\t\tTry\n" +
                        "\t\t\t\tconn.Open()\n" +
                        "\t\t\t\tcmd.ExecuteNonQuery()\n" +
                        "\t\t\tCatch\n" +
                        "\t\t\tFinally\n" +
                        "\t\t\t\tIf conn.State = ConnectionState.Open Then conn.Close()\n" +
                        "\t\t\t\tconn.Dispose()\n" +
                        "\t\t\t\tcmd.Dispose()\n" +
                        "\t\t\tEnd Try\n" +
                        "\t\tEnd Sub\n";
            }

            return ret;
        }

        private string CreateUpdateEntity()
        {
                string ret = "";
                string sqlupdate = "";
                string tValue = "";
                string m_value = "string ";
                string dataType = "";
                string primary = "";
                string where = "";

                SqlConnection conn = new SqlConnection(connStr);
                SqlCommand cmd = new SqlCommand("SELECT Column_Name,Data_Type From information_Schema.columns Where Table_Name='" + mTableName + "'", conn);
                SqlDataReader rdr;
                try
                {
                    sqlupdate = " \t\t\t s.AppendLine(\"" + " UPDATE " + mTableName + "\"" + ");\n" +
                   "\t\t\t s.AppendLine(\"" + "SET" + "\"" + ");\n";

                    conn.Open();
                    rdr = cmd.ExecuteReader();
                    primary = GetPrimaryKeyTable(mTableName);
                    while (rdr.Read())
                    {

                        dataType = rdr[1].ToString();
                        switch (dataType.Trim().ToLower())
                        {
                            case "varchar":
                                if (primary.Trim() == rdr[0].ToString().Trim())
                                {
                                    where ="\t\t\t s.AppendLine(\"" + rdr[0].ToString() + " =\"" + "+\"'\"+" + mTableName.Trim().ToLower() + "." + rdr[0].ToString() + " +" + "\"'\"" + ");\n";
                                }
                                tValue += "\t\t\t s.AppendLine(\"" + rdr[0].ToString() + " =\"" + "+\"'\"+" + mTableName.Trim().ToLower() + "." + rdr[0].ToString() + " +" + "\"',\"" + ");\n";
                                break;

                            case "decimal":
                                tValue += "\t\t\t s.AppendLine(\"" + rdr[0].ToString() + " =\"" + "+" + mTableName.Trim().ToLower() + "." + rdr[0].ToString() + " +" + "\",\"" + ");\n";

                                break;


                            case "datetime":
                                tValue += "\t\t\t s.AppendLine(\"" + rdr[0].ToString() + " =\"" + "+\"'\"+" + mTableName.Trim().ToLower() + "." + rdr[0].ToString() + " +" + "\"',\"" + ");\n";

                                break;

                            case "char":
                                tValue += "\t\t\t s.AppendLine(\"" + rdr[0].ToString() + " =\"" + "+\"'\"+" + mTableName.Trim().ToLower() + "." + rdr[0].ToString() + " +" + "\"',\"" + ");\n";

                                break;

                            case "text":
                                tValue += "\t\t\t s.AppendLine(\"" + rdr[0].ToString() + " =\"" + "+\"'\"+" + mTableName.Trim().ToLower() + "." + rdr[0].ToString() + " +" + "\"',\"" + ");\n";

                                break;

                            case "numeric":
                                tValue += "\t\t\t s.AppendLine(\"" + rdr[0].ToString() + " =\"" + "+" + mTableName.Trim().ToLower() + "." + rdr[0].ToString() + " +" + "\",\"" + ");\n";

                                break;

                            case "int":

                                if (primary.Trim() == rdr[0].ToString().Trim())
                                {
                                    where = "\t\t\t s.AppendLine(\"" + rdr[0].ToString() + " =\"" + "+" + mTableName.Trim().ToLower() + "." + rdr[0].ToString() + " +" + "\"\"" + ");\n";
                                }

                                tValue += "\t\t\t s.AppendLine(\"" + rdr[0].ToString() + " =\"" + "+" + mTableName.Trim().ToLower() + "." + rdr[0].ToString() + " +" + "\",\"" + ");\n";

                                break;


                        }

                        //tValue += "\t\t\t s.AppendLine(\"" + rdr[0].ToString() + " =\"" + "+\"'\"" + mTableName.Trim().ToLower() + "." + rdr[0].ToString() + " +" + "\"',\"" + ");\n";


                       }
                    tValue = tValue.Substring(0, tValue.Length - 5) + "\"" + ");";
                }
                catch
                {
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                    }
                    conn.Dispose();
                    cmd.Dispose();
                }


                ret += "\t\tpublic int Update(" + mTableName +" " + mTableName.ToLower() + ")\n" +
                       "\t\t{\n" +
                        "\t\t\t StringBuilder s = new StringBuilder();\n" +
                        sqlupdate +
                        tValue +"\n"+
                        "\t\t\t s.AppendLine(\"" + "WHERE" + "\"" + ");\n" +
                        //"\n\t s.AppendLine(\"" + GetPrimaryKeyTable(mTableName) + "]	 = @" + mTableName+"." + GetPrimaryKeyTable(mTableName) + "\n" +
                        where +"\n"+
                        "\n" +
                       "\t\t\t int result = -1;\n" +
                       "\t\t\t accessor = new Accessor(true, connString);\n" +
                       "\t\t\t accessor.CommandText = s.ToString()" + ";\n" +
                       "\t\t\t accessor.CommandType = CommandType.Text;\n" +
                       "\n" +
                        "\t\t\t result = Excutor.ExecuteNonQuery(accessor);\n" +
                        "\t\t\t if (result != -1)\n" +
                       "\t\t\t\t accessor.CommitTransaction();\n" +
                       "\t\t\t else\n" +
                       "\t\t\t\t accessor.RollBackTransaction();\n" +
                       "\n" +
                        "\t\t\t return result;\n" +
                        "\t\t}\n";

            return ret;
        }

        private string CreateDelete(string LanguageType)
        {
            string ret = "";
            if (LanguageType == "C#")
            {

                ret += "\t\tpublic void Delete(string ID)\n" +
                                "\t\t{\n" +
                                "\t\t\tSqlConnection conn = new SqlConnection(ConnectionString)\n" +
                                "\t\t\tSqlCommand cmd = new SqlCommand(\"Delete_" + mTableName + "\", conn);\n" +
                                "\t\t\tcmd.CommandType = CommandType.StoredProcedure;\n" +
                                "\t\t\tcmd.Parameters.AddWithValue(\"@ID\",ID);\n" +
                                "\n" +
                                "\t\t\ttry\n" +
                                "\t\t\t{\n" +
                                "\t\t\t\tconn.Open();\n" +
                                "\t\t\t\tcmd.ExecuteNonQuery();\n" +
                                "\t\t\t}\n" +
                                "\t\t\tcatch\n" +
                                "\t\t\t{}\n" +
                                "\t\t\tfinally\n" +
                                "\t\t\t{\n" +
                                "\t\t\t\tif (conn.State == ConnectionState.Open) conn.Close();\n" +
                                "\t\t\t\tconn.Dispose();\n" +
                                "\t\t\t\tcmd.Dispose();\n" +
                                "\t\t\t}\n" +
                                "\t\t}\n";
            }
            else if (LanguageType == "VB")
            {
                ret += "\t\tPublic Sub Delete(ByVal ID As String)\n" +
                           "\t\t\tDim conn As New SqlConnection(ConnectionString)\n" +
                           "\t\t\tDim cmd As New SqlCommand(\"Delete_" + mTableName + "\", conn)\n" +
                           "\t\t\tcmd.CommandType = CommandType.StoredProcedure\n" +
                           "\t\t\tcmd.Parameters.AddWithValue(\"@ID\",ID)\n" +
                           "\n" +
                           "\t\t\tTry\n" +
                           "\t\t\t\tconn.Open()\n" +
                           "\t\t\t\tcmd.ExecuteNonQuery()\n" +
                           "\t\t\tCatch\n" +
                           "\t\t\tFinally\n" +
                           "\t\t\t\tIf conn.State = ConnectionState.Open Then conn.Close()\n" +
                           "\t\t\t\tconn.Dispose()\n" +
                           "\t\t\t\tcmd.Dispose()\n" +
                           "\t\t\tEnd Try\n" +
                           "\t\tEnd Sub\n";
            }

            return ret;
        }

        private string CreateDeleteEntity()
        {
            string ret = "";


            ret +=      "\t\tpublic int Delete(" + mTableName + " " + mTableName.ToLower() + ")\n" +
                        "\t\t{\n" +
                         "\t\t\t StringBuilder s = new StringBuilder();\n" +
                        ReturnSQLDelete()+
                        "\t\t\t int result = -1;\n" +
                        "\t\t\t accessor = new Accessor(true, connString);\n" +
                        "\t\t\t accessor.CommandText = s.ToString()" + ";\n" +
                        "\t\t\t accessor.CommandType = CommandType.Text;\n" +
                        //"\t\t\t accessor.Add(@\"" + GetPrimaryKeyTable(mTableName) + "\" ," + mTableName.ToLower() + "." + GetPrimaryKeyTable(mTableName) + ");\n" +
                        "\n" +
                        "\t\t\t result = Excutor.ExecuteNonQuery(accessor);\n" +
                        "\t\t\t if (result != -1)\n" +
                        "\t\t\t\t accessor.CommitTransaction();\n" +
                        "\t\t\t else\n" +
                        "\t\t\t\t accessor.RollBackTransaction();\n" +
                        "\t\t\t return result;\n" +
                        "\t\t}\n";

            return ret;
           
        }

        public string ReturnSQLDelete()
        {

            string dataType = "";
            string primary = "";
            string where = "";
            //string sqlupdate = "";

            SqlConnection conn = new SqlConnection(connStr);
            SqlCommand cmd = new SqlCommand("SELECT Column_Name,Data_Type From information_Schema.columns Where Table_Name='" + mTableName + "'", conn);
            SqlDataReader rdr;
            try
            {
                //sqlupdate = " \t\t\t s.AppendLine(\"" + " UPDATE " + mTableName + "\"" + ");\n" +
               

                conn.Open();
                rdr = cmd.ExecuteReader();
                primary = GetPrimaryKeyTable(mTableName);
                while (rdr.Read())
                {

                    dataType = rdr[1].ToString();
                    switch (dataType.Trim().ToLower())
                    {
                        case "varchar":
                            if (primary.Trim() == rdr[0].ToString().Trim())
                            {
                                where = "\t\t\t s.AppendLine(\"" + rdr[0].ToString() + " =\"" + "+\"'\"+" + mTableName.Trim().ToLower() + "." + rdr[0].ToString() + " +" + "\"'\"" + ");\n";
                            }
                            //tValue += "\t\t\t s.AppendLine(\"" + rdr[0].ToString() + " =\"" + "+\"'\"+" + mTableName.Trim().ToLower() + "." + rdr[0].ToString() + " +" + "\"',\"" + ");\n";
                            break;

                       

                        case "int":

                            if (primary.Trim() == rdr[0].ToString().Trim())
                            {
                                where = "\t\t\t s.AppendLine(\"" + rdr[0].ToString() + " =\"" + "+" + mTableName.Trim().ToLower() + "." + rdr[0].ToString() + " +" + "\"\"" + ");\n";
                            }

                            //tValue += "\t\t\t s.AppendLine(\"" + rdr[0].ToString() + " =\"" + "+" + mTableName.Trim().ToLower() + "." + rdr[0].ToString() + " +" + "\"\"" + ");\n";

                            break;


                    }

                    //tValue += "\t\t\t s.AppendLine(\"" + rdr[0].ToString() + " =\"" + "+\"'\"" + mTableName.Trim().ToLower() + "." + rdr[0].ToString() + " +" + "\"',\"" + ");\n";


                }
                
            }
            catch
            {
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
                conn.Dispose();
                cmd.Dispose();
            }




            string ret = "";
            TableSQL tbl = new TableSQL();
            tbl = tbl.GetDataByColumn(mTableName, connStr,GetPrimaryKeyTable(mTableName));
            string tValue = "";
            if (tbl.MaximumLength.Trim()=="")
                  tValue += "\t@" +tbl.ColumnName + "\t\t\t\t\t\t[" + tbl.DataType + "]" + "\n";
            else
                 tValue += "\t@" + tbl.ColumnName  + "\t\t\t\t\t\t[" + tbl.DataType  + "](" + tbl.MaximumLength  + ")" + "\n";


            //ret = "CREATE PROCEDURE [" + mTableName + "_Delete]\n" +

             ret = " \t\t\t s.AppendLine(\"" + " DELETE " + mTableName + "\"" + ");\n" +
                    " \t\t\t s.AppendLine(\"" + " WHERE " + "\"" + ");\n" +
                    where + "\n";
                    //"\t( [" + GetPrimaryKeyTable(mTableName) + "]	 = @" + GetPrimaryKeyTable(mTableName) + ")\n";
            return ret;
        }

        private string GetPrimaryKeyTable(string mTable)
        {
            string pk = string.Empty;
            SqlConnection conn = new SqlConnection(connStr);

             string query = @"SELECT u.COLUMN_NAME, c.CONSTRAINT_NAME
            FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS c INNER JOIN
            INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS u ON c.CONSTRAINT_NAME = u.CONSTRAINT_NAME
            where u.TABLE_NAME = '" + mTable + "'AND c.TABLE_NAME = '"+mTable+"' and c.CONSTRAINT_TYPE = 'PRIMARY KEY'";

            SqlCommand cmd = new SqlCommand(query, conn);

            

            SqlDataReader rdr;
            try
            {
                conn.Open();
                rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    pk = rdr[0].ToString();// +"|";
                }
            }
            catch
            {

            }

            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
                conn.Dispose();
                cmd.Dispose();
            }

            //pk = pk.Trim().Substring(0,pk.Length-1);
            return pk;
        }
        public string ReturnSQLSelect()
        {
            string ret = "";
            string m_value = "";

            SqlConnection conn = new SqlConnection(connStr);
            SqlCommand cmd = new SqlCommand("SELECT Column_Name,Data_Type,Character_Maximum_Length From information_Schema.columns Where Table_Name='" + mTableName + "'", conn);
            SqlDataReader rdr;
            try
            {
                conn.Open();
                rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    if (rdr[0].ToString().Length > 4)
                    {
                        if (rdr[0].ToString().Substring(rdr[0].ToString().Length - 4, 4) == "Code")
                            m_value += "\t\t(Select Title From " + rdr[0].ToString().Substring(0, rdr[0].ToString().Length - 5) + " Where Code = " + rdr[0].ToString() + ")\tAS\t'" + rdr[0].ToString() + "',\n";
                        else
                            m_value += "\t\t\t s.AppendLine(\"" + rdr[0].ToString() + "\tAS\t'" + rdr[0].ToString() + "',\"" + ");\n";
                    }
                    else
                        m_value += "\t\t\t s.AppendLine(\"" + rdr[0].ToString() + "\tAS\t'" + rdr[0].ToString() + "',\""+");\n";
                }
                m_value = m_value.Substring(0, m_value.Length - 5) + "\"" + ");";

            }
            catch
            { }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
                conn.Dispose();
                cmd.Dispose();
            }

           // ret = "CREATE PROCEDURE [" + mTableName + "_SelectAll]\n" +
             //       "AS\n" +
            ret = "\n\t\t\t s.AppendLine(\"" + " Select " + "\"" + ");\n" +
                     m_value + "\n" +
                    "\t\t\t s.AppendLine(\"" + " From \t" + mTableName + "\"" + ");\n\n";


            return ret;
        }

        public string ReturnSQLSelectByPK()
        {
            string ret = "";
            string m_value = "";

            string m_param = "";
            string m_kolom ="";
            string dataType = "";
            string tValue = "";
            string primary = "";
            string where = "";
            m_kolom = GetPrimaryKeyTable(mTableName);

            SqlConnection conn = new SqlConnection(connStr);
            SqlCommand cmd = new SqlCommand("SELECT Column_Name,Data_Type,Character_Maximum_Length From information_Schema.columns Where Table_Name='" + mTableName + "'", conn);
            SqlDataReader rdr;
            try
            {
                conn.Open();
                rdr = cmd.ExecuteReader();

                primary = GetPrimaryKeyTable(mTableName);


                while (rdr.Read())
                {


                    if (rdr[0].ToString().Length > 4)
                    {
                        if (rdr[0].ToString().Substring(rdr[0].ToString().Length - 4, 4) == "Code")
                            m_value += "\t\t(Select Title From " + rdr[0].ToString().Substring(0, rdr[0].ToString().Length - 5) + " Where Code = " + rdr[0].ToString() + ")\tAS\t'" + rdr[0].ToString() + "',\n";
                        else
                            m_value += "\t\t\t s.AppendLine(\"" + rdr[0].ToString() + "\tAS\t'" + rdr[0].ToString() + "',\"" + ");\n";
                    }
                    else
                        m_value += "\t\t\t s.AppendLine(\"" + rdr[0].ToString() + "\tAS\t'" + rdr[0].ToString() + "',\"" + ");\n";



                    dataType = rdr[1].ToString();
                    switch (dataType.Trim().ToLower())
                    {
                        case "varchar":
                            if (primary.Trim() == rdr[0].ToString().Trim())
                            {
                                where = "\t\t\t s.AppendLine(\"" + rdr[0].ToString() + " =\"" + "+\"'\"+" + mTableName.Trim().ToLower() + "." + rdr[0].ToString() + " +" + "\"'\"" + ");\n";
                            }
                            //tValue += "\t\t\t s.AppendLine(\"" + rdr[0].ToString() + " =\"" + "+\"'\"+" + mTableName.Trim().ToLower() + "." + rdr[0].ToString() + " +" + "\"',\"" + ");\n";
                            break;



                        case "int":

                            if (primary.Trim() == rdr[0].ToString().Trim())
                            {
                                where = "\t\t\t s.AppendLine(\"" + rdr[0].ToString() + " =\"" + "+" + mTableName.Trim().ToLower() + "." + rdr[0].ToString() + " +" + "\"\"" + ");\n";
                            }

                            //tValue += "\t\t\t s.AppendLine(\"" + rdr[0].ToString() + " =\"" + "+" + mTableName.Trim().ToLower() + "." + rdr[0].ToString() + " +" + "\"\"" + ");\n";

                            break;


                    }

                }
                m_value = m_value.Substring(0, m_value.Length - 5) + "\"" + ");";

            }
            catch
            { }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
                conn.Dispose();
                cmd.Dispose();
            }


            // ret = "CREATE PROCEDURE [" + mTableName + "_SelectAll]\n" +
            //       "AS\n" +
             ret = "\n\t\t\t s.AppendLine(\"" + " Select " + "\"" + ");\n" +
                     m_value + "\n" +
                    "\t\t\t s.AppendLine(\"" + " From \t" + mTableName + "\"" + ");\n"+
                    " \t\t\t s.AppendLine(\"" + " WHERE " + "\"" + ");\n" +
                    where + "\n";


            return ret;
        }

        public string ReturnSQLUpdate()
        {
            string ret = "";
            string tValue = "";
            string tValue1 = "";
            string m_value = "";

            SqlConnection conn = new SqlConnection(connStr);
            SqlCommand cmd = new SqlCommand("SELECT Column_Name,Data_Type,Character_Maximum_Length From information_Schema.columns Where Table_Name='" + mTableName + "'", conn);
            SqlDataReader rdr;
            try
            {
                conn.Open();
                rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    m_value += "\t\t" + rdr[0].ToString() + "\t\t\t=\t@" + rdr[0].ToString() + ",\n";
                    if (rdr[2].ToString() == "" || rdr[1].ToString() == "ntext" || rdr[1].ToString() == "image")
                        tValue += "\t@" + rdr[0].ToString() + "\t\t\t\t\t\t[" + rdr[1].ToString() + "]" + ",\n";
                    else
                        tValue += "\t@" + rdr[0].ToString() + "\t\t\t\t\t\t[" + rdr[1].ToString() + "](" + rdr[2].ToString() + ")" + ",\n";
                    tValue1 += "\t@" + rdr[0].ToString() + ",\n";
                }
                m_value = m_value.Substring(0, m_value.Length - 2);
                tValue = tValue.Substring(0, tValue.Length - 2);
                tValue1 = tValue1.Substring(0, tValue1.Length - 2);
            }
            catch
            { }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
                conn.Dispose();
                cmd.Dispose();
            }

            //ret = "CREATE PROCEDURE [" + mTableName + "_Update]\n" +
                    //"(\n" + tValue + "\n)\n" +
                    //"AS 
            ret = " \t\t\t s.AppendLine(\"" + " UPDATE " + mTableName + "\"" + ");\n" +
                    "\t\t\t s.AppendLine(\"" + "SET" + "\"" + ");\n" +
                    m_value +
                    "\n" +
                    "\t\t\t s.AppendLine(\"" + "WHERE" + "\"" + ");\n" +
                    "(\n\t [" + GetPrimaryKeyTable(mTableName) + "]	 = @" + GetPrimaryKeyTable(mTableName) + "\n)";


            //ret = "\t\t\t s.AppendLine(\"" + " INSERT INTO " + mTableName + "\"" + ");\n" +
            //       "\t\t\t s.AppendLine(\"" + "(" + "\"" + ");\n" +
            //       m_value +
            //       "\n\t\t\t s.AppendLine(\"" + ")" + "\"" + ");\n" +
            //       "\t\t\t s.AppendLine(\"" + "VALUES(" + "\"" + ");\n";
            ////"\t(\n" + tValue1 + "\n\t)\n";

           

            return ret;
        }

        public string ReturnSQLInsert()
        {
            string ret = "";

            string tValue = "";
            string tValue1 = "";
            string m_value = "";

            string primary = "";
            string dataType = "";

            SqlConnection conn = new SqlConnection(connStr);
            SqlCommand cmd = new SqlCommand("SELECT Column_Name,Data_Type,Character_Maximum_Length From information_Schema.columns Where Table_Name='" + mTableName + "'", conn);
            SqlDataReader rdr;
            try
            {
                conn.Open();
                rdr = cmd.ExecuteReader();

                int jml = rdr.FieldCount;
                int field = 0;
                primary = GetPrimaryKeyTable(mTableName);
                while (rdr.Read())
                {
                        m_value += "\t\t\t s.AppendLine(\"" + rdr[0].ToString() + ",\");\n";


                        dataType = rdr[1].ToString();
                        switch (dataType.Trim().ToLower())
                        {
                            case "varchar":
                                if (primary.Trim() == rdr[0].ToString().Trim())
                                {
                                    //where = "\t\t\t s.AppendLine(\"" + rdr[0].ToString() + " =\"" + "+\"'\"+" + mTableName.Trim().ToLower() + "." + rdr[0].ToString() + " +" + "\"'\"" + ");\n";
                                }
                                //tValue += "\t\t\t s.AppendLine(\"" + rdr[0].ToString() + " =\"" + "+\"'\"+" + mTableName.Trim().ToLower() + "." + rdr[0].ToString() + " +" + "\"',\"" + ");\n";
                                break;



                            case "int":

                                if (primary.Trim() == rdr[0].ToString().Trim())
                                {
                                    //where = "\t\t\t s.AppendLine(\"" + rdr[0].ToString() + " =\"" + "+" + mTableName.Trim().ToLower() + "." + rdr[0].ToString() + " +" + "\"\"" + ");\n";
                                }

                                //tValue += "\t\t\t s.AppendLine(\"" + rdr[0].ToString() + " =\"" + "+" + mTableName.Trim().ToLower() + "." + rdr[0].ToString() + " +" + "\"\"" + ");\n";

                                break;


                        }

                   
                }
                m_value = m_value.Substring(0, m_value.Length - 5)+"\""+");";
               
            }
            catch
            { }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
                conn.Dispose();
                cmd.Dispose();
            }

           // ret = "CREATE PROCEDURE [" + mTableName + "_Insert]\n" +
                   // "\t(\n" + tValue + "\n\t)\n" +
                    //"AS
            ret = "\t\t\t s.AppendLine(\""+" INSERT INTO " + mTableName + "\""+");\n" +
                    "\t\t\t s.AppendLine(\""+"("+"\""+");\n" + 
                    m_value +
                    "\n\t\t\t s.AppendLine(\"" + ")" + "\"" + ");\n" +
                    "\t\t\t s.AppendLine(\""+ "VALUES("+"\""+");\n";
                    //"\t(\n" + tValue1 + "\n\t)\n";

            return ret;
        }

        public string CreateSelect(string LanguageType)
        {
            string ret = "";

            if (LanguageType == "C#")
            {
                ret = "\t\tpublic DataSet SelectRecords()\n" +
                       "\t\t{\n" +
                       "\t\t\tSqlConnection conn = new SqlConnection(ConnectionString)\n" +
                       "\t\t\tSqlDataAdapter cmd = new SqlDataAdapter(\"SELECT * FROM Select_" + mTableName + "\", conn);\n" +
                       "\t\t\tDataSet dts = new DataSet();\n" +
                       "\t\t\ttry\n" +
                       "\t\t\t{\n" +
                       "\t\t\t\tconn.Open();\n" +
                       "\t\t\t\tcmd.Fill(dts);\n" +
                       "\t\t\t\treturn dts;\n" +
                       "\t\t\t}\n" +
                       "\t\t\tcatch\n" +
                       "\t\t\t{ }\n" +
                       "\t\t\tfinally\n" +
                       "\t\t\t{\n" +
                       "\t\t\t\tif (conn.State == ConnectionState.Open) conn.Close();\n" +
                       "\t\t\t\tcmd.Dispose();\n" +
                       "\t\t\t}\n" +
                        "\t\t\treturn dts;\n" +
                       "\t\t}\n";
            }
            else if (LanguageType == "VB")
            {
                ret += "\t\tPublic Function SelectRecords() As DataSet\n" +
                           "\t\t\tDim conn As New SqlConnection(ConnectionString)\n" +
                           "\t\t\tDim cmd As New SqlDataAdapter(\"SELECT * FROM Select_" + mTableName + "\", conn)\n" +
                           "\t\t\tDim dts As New DataSet()\n" +
                           "\n" +
                           "\t\t\tTry\n" +
                           "\t\t\t\tconn.Open()\n" +
                           "\t\t\t\tcmd.Fill(dts)\n" +
                           "\t\t\t\tReturn dts\n" +
                           "\t\t\tCatch\n" +
                           "\t\t\t\tReturn Null\n" +
                           "\t\t\tFinally\n" +
                           "\t\t\t\tIf conn.State = ConnectionState.Open Then conn.Close()\n" +
                           "\t\t\t\tconn.Dispose()\n" +
                           "\t\t\t\tcmd.Dispose()\n" +
                           "\t\t\tEnd Try\n" +
                           "\t\tEnd Function\n";
            }

            return ret;
        }

        public string ReturnClass()
        {

            StringBuilder ret = new StringBuilder();
            ret.AppendLine("using System;");// +
            if (name.Trim().Length > 1)
            {
                ret.AppendLine("namespace " + name + "");
                ret.AppendLine("{");
            }
            ret.AppendLine("\t public class " + mTableName);
            ret.AppendLine("\t{");
            ret.AppendLine(CreateProperty("C#"));
            ret.AppendLine("\n");
            if (name.Trim().Length > 1)
                ret.AppendLine("\t}");
            ret.AppendLine("}");

            return ret.ToString();
        }

        public string ReturnEntityClass()
        {
            MaxID = false;
            //string ret = "";
            StringBuilder ret = new StringBuilder();

            ret.AppendLine("using System;");
            ret.AppendLine("using System.Collections.Generic;");
            ret.AppendLine("using System.Text;");
            ret.AppendLine("using System.Data;");
            ret.AppendLine("using System.Data.SqlClient;\n\n");
            ret.AppendLine("using " + nameprojek + ".Object;");
            ret.AppendLine("using "+ nameprojek + ".DAL;");

            if (name.Trim().Length > 1)
            {
                ret.AppendLine("namespace " + name + "");
                ret.AppendLine("{");
            }
            ret.AppendLine("\tpublic class " + mTableName+"Entity");
            ret.AppendLine("\t{");
            //ret.AppendLine("\n");
            ret.AppendLine("\t\tprivate Accessor accessor;");
            ret.AppendLine("\t\tprivate string connString;\n");
            ret.AppendLine("\t\tpublic " + mTableName + "Entity (string _connString)");
            ret.AppendLine("\t\t{");
            ret.AppendLine("\t\t\t this.connString = _connString;");
            ret.AppendLine("\t\t}\n");
            ret.AppendLine(CreateInsertEntity());
          
            ret.AppendLine(CreateUpdateEntity());
          
            ret.AppendLine(CreateDeleteEntity());
            
            ret.AppendLine(CreateSelectByIDEntity());
            
            ret.AppendLine(CreateSelectAll());

            if (MaxID)
                ret.AppendLine(CreateGetMaxID(mKolomName, mTableName));
             
             if (name.Trim().Length > 1)
                ret.AppendLine("\t}");
            ret.AppendLine("}");
            return ret.ToString();
        }

        private string CreateSelectByIDEntity()
        {
            string ret = "";

            string tValue = "";
            string m_value = "string ";

            SqlConnection conn = new SqlConnection(connStr);
            SqlCommand cmd = new SqlCommand("SELECT Column_Name,Data_Type From information_Schema.columns Where Table_Name='" + mTableName + "'", conn);
            SqlDataReader rdr;
            try
            {
                conn.Open();
                rdr = cmd.ExecuteReader();
                int indeks = 0;
                string dataType = "";
                while (rdr.Read())
                {
                    //m_value += rdr[0].ToString() + " , string ";
                    //tValue += "\t\t\t accessor.Add(\"@" + rdr[0].ToString() + "\" ," + mTableName.ToLower().Trim() + "." + rdr[0].ToString() + " );\n";
                    dataType = rdr[1].ToString();
                    switch (dataType.Trim().ToLower())
                    {
                        case "varchar":
                            dataType = "GetString";
                            break;

                        case "decimal":
                            dataType = "GetDecimal";
                            break;

                        case "datetime":
                            dataType = "GetDateTime";
                            break;

                        case "char":
                            dataType = "GetString";
                            break;

                        case "text":
                            dataType = "GetString";
                            break;

                        case "numeric":
                            dataType = "GetInt32";
                            break;

                        case "int":
                            dataType = "GetInt32";
                            break;


                        default:
                            dataType = "GetString";
                            break;
                    }

                    m_value += rdr[0].ToString() + " , string ";
                    tValue += "\t\t\t\t\t\t result." + rdr[0].ToString() + " = " + "rdr." + dataType + "(" + indeks.ToString() + ");\n";


                    indeks++;

                }
                m_value = m_value.Substring(0, m_value.Length - 10);
            }
            catch
            { }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
                conn.Dispose();
                cmd.Dispose();
            }


            ret += "\t\tpublic " + mTableName.Trim() +" GetDataByID(" + mTableName + " " + mTableName.ToLower() + ")\n" +
                   "\t\t{\n" +
                    "\t\t\t StringBuilder s = new StringBuilder();\n" +
                   "\t\t\t "+ mTableName + " result = new "+mTableName+"();\n" +
                   //"\t\t\t accessor.Add(@\"" + GetPrimaryKeyTable(mTableName) + "\" ," + mTableName.ToLower() + "." + GetPrimaryKeyTable(mTableName) + ");\n" +
                   ReturnSQLSelectByPK()+
                   "\n" +
                   "\t\t\t accessor = new Accessor(true, connString);\n" +
                   "\t\t\t accessor.CommandText = s.ToString();\n" +
                   "\t\t\t accessor.CommandType = CommandType.Text;\n" +
                   "\t\t\t SqlDataReader rdr = Excutor.GetDataReader(accessor);\n"+
                   "\t\t\t if (rdr.HasRows)\n"+
                   "\t\t\t {\n"+
                   "\t\t\t\t while (rdr.Read())\n"+
                   "\t\t\t\t\t {\n"+
                   tValue +
                   "\t\t\t\t\t  }\n"+
                   "\t\t\t}\n" +
                   "\t\t\t return result;\n" +
                   "\t\t}\n";
            return ret;
        }

        private string CreateSelectAll()
        {
            string ret = "";

            string tValue = "";
            string m_value = "string ";

            SqlConnection conn = new SqlConnection(connStr);
            SqlCommand cmd = new SqlCommand("SELECT Column_Name,Data_Type From information_Schema.columns Where Table_Name='" + mTableName + "'", conn);
            SqlDataReader rdr;
            try
            {
                conn.Open();
                rdr = cmd.ExecuteReader();
                int indeks = 0;
                string dataType = "";
                while (rdr.Read())
                {
                    //m_value += rdr[0].ToString() + " , string ";
                    //tValue += "\t\t\t accessor.Add(\"@" + rdr[0].ToString() + "\" ," + mTableName.ToLower().Trim() + "." + rdr[0].ToString() + " );\n";
                    dataType = rdr[1].ToString();
                    switch (dataType.Trim().ToLower())
                    {
                        case "varchar":
                            dataType = "GetString";
                            break;

                        case "decimal":
                            dataType = "GetDecimal";
                            break;


                        case "datetime":
                            dataType = "GetDateTime";
                            break;

                        case "char":
                            dataType = "GetString";
                            break;

                        case "text":
                            dataType = "GetString";
                            break;

                        case "numeric":
                            dataType = "GetInt32";
                            break;

                        case "int":
                            dataType = "GetInt32";
                            break;


                        default:
                            dataType = "GetString";
                            break;
                    }

                    m_value += rdr[0].ToString() + " , string ";
                    tValue += "\t\t\t\t\t\t obj." + rdr[0].ToString() + " = " + "rdr." + dataType + "(" + indeks.ToString() + ");\n";


                    indeks++;

                }
                m_value = m_value.Substring(0, m_value.Length - 10);
            }
            catch
            { }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
                conn.Dispose();
                cmd.Dispose();
            }


            ret += "\t\tpublic List<" + mTableName.Trim() + "> GetDataAll(" + ")\n" +
                   "\t\t{\n" +
                   "\t\t\t List<" + mTableName + "> result = new List<" + mTableName + ">();\n" +
                    "\t\t\t StringBuilder s = new StringBuilder();\n" +
                    ReturnSQLSelect() +
                   "\t\t\t accessor = new Accessor(true, connString);\n" +
                   "\t\t\t accessor.CommandText = s.ToString();\n" +//\"" + mTableName.Trim() + "_SelectAll\"" + ";\n" +
                   "\t\t\t accessor.CommandType = CommandType.Text;\n" +
                   //"\t\t\t accessor.Add(@\"" + GetPrimaryKeyTable(mTableName) + "\" ," + mTableName.ToLower() + "." + GetPrimaryKeyTable(mTableName) + ");\n" +
                   "\n" +
                   "\t\t\t SqlDataReader rdr = Excutor.GetDataReader(accessor);\n" +
                   "\t\t\t if (rdr.HasRows)\n" +
                   "\t\t\t {\n" +
                   "\t\t\t\t while (rdr.Read())\n" +
                   "\t\t\t\t\t {\n" +
                   "\t\t\t\t\t\t "+mTableName+" obj = new "+mTableName+"();\n" +
                   tValue +
                   "\t\t\t\t\t\t result.Add(obj);\n"+
                   "\t\t\t\t\t  }\n" +
                   "\t\t\t}\n" +
                   "\t\t\t return result;\n" +
                   "\t\t}\n";
            return ret;
        }


        #region AJAXHANDLER

        public static string PropCaseStartLower(string strText)
        {
            string retVal = PropCase(strText);
            retVal = retVal.Substring(0, 1).ToLower() + retVal.Substring(1, (retVal.Length - 1));
            return retVal;
        }

        public static string PropCase(string strText)
        {
            return new CultureInfo("en").TextInfo.ToTitleCase(strText.ToLower());
        }


        public string ReturnClassAjaxHandler()
        {

            StringBuilder ret = new StringBuilder();
            ret.AppendLine("<%@ WebHandler Language="+"\""+"C#"+"\""+" Class="+"\""+"RTGS.Web.ajaxhandler.usermanagement.sys_t_user"+"\""+" %>");

            ret.AppendLine("using System;");
            ret.AppendLine("using System.Collections.Generic;");
            ret.AppendLine("using System.Linq;");
            ret.AppendLine("using System.Web;");
            ret.AppendLine("using System.Configuration;");
            ret.AppendLine("using RTGS.Library;");
            ret.AppendLine("using RTGS.Library.DataAccess.UserManagement;");
            ret.AppendLine("using RTGS.Library.Entities.UserManagement;");
            ret.AppendLine("using RTGS.Library.Enum;");
            ret.AppendLine("using RTGS.Web.support;");
            ret.AppendLine("namespace RTGS.Web.ajaxhandler.usermanagement");
            ret.AppendLine("\t {");
            ret.AppendLine("\t\tpublic class " + PropCaseStartLower(mTableName) + " : BaseHandler");
            ret.AppendLine("\t \t{");
            ret.AppendLine("\t\t  private " + PropCaseStartLower(mTableName).Replace("_", "") + "Repo " + PropCaseStartLower(mTableName.Replace("_", "")) + "Dal = new " + PropCaseStartLower(mTableName).Replace("_", "") + "Repo(ConfigurationManager.ConnectionStrings[" + "\"" + "Rtgs" + "\"" + "].ConnectionString);");
            ret.AppendLine("\t\t  private sysTLogRepo logDal = new sysTLogRepo(ConfigurationManager.ConnectionStrings[" + "\"" + "Rtgs" + "\"" + "].ConnectionString);");
            ret.AppendLine("\t\t  private sysTLog log = new sysTLog();");
            ret.AppendLine("\t\t  private string MethodName = string.Empty;");
            ret.AppendLine("\t\t  public override void ProcessRequest(HttpContext context)");
            ret.AppendLine("\t\t\t{");
            ret.AppendLine("\t\t\t\tbase.ProcessRequest(context);");
            ret.AppendLine("\t\t\t\tMethodName = context.Request.Params[" + "\"" + "method" + "\"" + "];");
            ret.AppendLine("\t\t\t\tcontext.Response.Clear();");
            ret.AppendLine("\t\t\t\tcontext.Response.Buffer = true;");
            ret.AppendLine("\t\t\t\tswitch (MethodName.ToLower())");
            ret.AppendLine("\t\t\t\t{");
            ret.AppendLine("\t\t\t\t\tcase (" + "\"" + "insert" + "\"" + "):");
            ret.AppendLine("\t\t\t\t\tcontext.Response.Write(Insert(context));");
            ret.AppendLine("\t\t\t\t\tbreak;");
            ret.AppendLine("\t\t\t\t\tcase (" + "\"" + "update" + "\"" + "):");
            ret.AppendLine("\t\t\t\t\tcontext.Response.Write(Update(context));");
            ret.AppendLine("\t\t\t\t\tbreak;");
            ret.AppendLine("\t\t\t\t\tcase (" + "\"" + "delete" + "\"" + "):");
            ret.AppendLine("\t\t\t\t\tcontext.Response.Write(Delete(context));");
            ret.AppendLine("\t\t\t\t\tbreak;");
            ret.AppendLine("\t\t\t\t\tcase (" + "\"" + "getdata" + "\"" + "):");
            ret.AppendLine("\t\t\t\t\tcontext.Response.Write(GetData(context));");
            ret.AppendLine("\t\t\t\t\tbreak;");
            ret.AppendLine("\t\t\t\t\tdefault:");
            ret.AppendLine("\t\t\t\t\t// do nothing");
            ret.AppendLine("\t\t\t\t\tbreak;");
            ret.AppendLine("\t\t\t\t}");
            ret.AppendLine("\t\t\t\tcontext.Response.Flush();");
            ret.AppendLine("\t\t\t\tcontext.Response.End();");
            ret.AppendLine("\t\t\t}");
            ret.AppendLine("\n");
            ret.AppendLine("\t\t public override bool IsReusable");
            ret.AppendLine("\t\t\t{");
            ret.AppendLine("\t\t\t\tget");
            ret.AppendLine("\t\t\t\t{");
            ret.AppendLine("\t\t\t\t\treturn false;");
            ret.AppendLine("\t\t\t\t}");
            ret.AppendLine("\t\t\t}");
            ret.AppendLine("\n");
            ret.AppendLine("\n");
            ret.AppendLine("#region CRUD Handler");
            ret.AppendLine("\n");
            ret.AppendLine(CreateInsertAjaxHandler());
            ret.AppendLine("\n");
            ret.AppendLine(CreateUpdateAjaxHandler());
            ret.AppendLine("\n");
            ret.AppendLine(CreateDeleteAjaxHandler());
            ret.AppendLine("#endregion");
            ret.AppendLine("\t\t}");
            ret.AppendLine("\t}");


           
           
         

            return ret.ToString();
        }

        #endregion


    }
}
